﻿using INTEX.DataBase.Tools;
using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using WPFExcelReport.Helpers;
namespace WPFExcelReport
{
    public class CaseInsensitiveComparer : IComparer<object>
    {
        public int Compare(object x, object y)
        {
            return string.Compare((string)x, (string)y, StringComparison.OrdinalIgnoreCase);
        }
    }
    class JLTSMPCOWSReport : BaseReport
    {
        #region ----------------PROPERTIES----------------------------------
        ApplicationBase app
        {
            get
            {
                return ApplicationBase.Instance;
            }
        }
        INTEXDBC INTEXDBC
        {
            get
            {
                return app.INTEXDBC;
            }
        }
        public DataSet setUI = new DataSet();  
        string DocType;  
        #endregion
        public JLTSMPCOWSReport()
            : base()
        {
        }
    
        public override void WriteExcelReport(string fileType)
        {
            bool IsFTP = false;
            try
            {
                if (exAgent == null)
                    exAgent = new ExcelX.ExcelLib.ExcelAgent();
                Root = "C:\\IntexReport";
                Helpers.Helper.GetDate(ref date, ref year, ref month, ref day);
                dsSource.Merge(INTEXDBC.QueryHRSTAMM(null, 3));
                strCompany = "JLT";
                strExcelFile = @"JLT\JLTCOWS.xls";
                Standard = "COWS";
                TemplatePath = Path.Combine(PathHelper.ApplicationPath, @"Report\JLT\JLTCOWS.xls");
                destfolder = SetFolder(Root, "销售预算");
                DataRow dwOrder = dsSource.Tables["ICC_VKPRICE"].Rows[0];
                string KRS = dwOrder["VKPAUFKRS"].ToString().Trim();
                string LFD = dwOrder["VKPAUFLFD"].ToString().Trim();
                string newfilename = KRS.Trim() + "-" + Helpers.Helper.GetSubRollNum(LFD);
                exAgent.CheckWorkBook(newfilename);
                ServerShareFile(KRS + LFD, fileType);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                exAgent.Save();
                exAgent.Close();
                exAgent = null;
            }
        }
        private void FillinRange()
        {
            try
            {
                double rate = 0;
                int vkpclass = 0;
                int page = 0;
                DataRow dwVKP;
                double vkqty = 0;
                string qty = "";
                dwVKP = dsSource.Tables["ICC_VKPRICE"].Rows[0];
                double exchange = Convert.ToDouble(dwVKP["USD2RMB"].ToString());
                exchange = exchange * 1.16;
                double weftdensity = Convert.ToDouble(dwVKP["MVKWEFTDEN"].ToString());
                if (weftdensity == 0)
                    weftdensity = 1;

                Dictionary<int, int> pages = new Dictionary<int, int>();
                var query = from DataRow vkp in dsSource.Tables["VKPOS"].AsEnumerable()
                            orderby Convert.ToDouble(vkp["VKPMENGE1"].ToString()) descending
                            select vkp;
                foreach (DataRow vkp in query)
                {
                    #region=====Initial========
                    double MCOSTMY = Convert.ToDouble(dwVKP["MCOSTMY"].ToString());
                    double MCOSTDY = Convert.ToDouble(dwVKP["MCOSTDY"].ToString());
                    double MCOSTPROD = Convert.ToDouble(dwVKP["MCOSTPROD"].ToString());
                    double costorder = Convert.ToDouble(dwVKP["MCOSTAGENT"].ToString())
                        + Convert.ToDouble(dwVKP["MCOSTSHIP"].ToString())
                        + Convert.ToDouble(dwVKP["MCOSTFLIGHT"].ToString())
                        + Convert.ToDouble(dwVKP["MCOSTADD"].ToString());

                    #endregion
                    vkpclass = 0;
                    page = 3;
                    vkqty = 0.01 * Convert.ToDouble(vkp["VKPMENGE1"].ToString());
                    if (dwVKP["VKPUNIT"].ToString().Trim().Contains("M"))
                    {
                        vkqty = Math.Round(vkqty / 0.9144);
                    }

                    if (vkqty < 3000)
                    {
                        vkpclass = 1;
                        rate = 0.1;
                    }
                    if (vkqty < 2000)
                    {
                        vkpclass = 2;
                        rate = 0.2;
                    }
                    if (vkqty < 1000)
                    {
                        vkpclass = 3;
                        rate = 0.5;
                    }
                    if (vkqty < 500)
                    {
                        vkpclass = 4;
                        rate = 1;
                    }
                    if (pages.ContainsKey(vkpclass))
                    {
                        page = pages[vkpclass];
                    }
                    else
                    {
                        qty = "";
                        page = 3 + pages.Count() * 2;
                        pages.Add(vkpclass, page);
                    }

                    rate = 0;
                    MCOSTMY = Math.Round(MCOSTMY * (1 + rate), 2);
                    MCOSTDY = Math.Round(MCOSTDY * (1 + rate), 2);
                    double valuem = MCOSTMY + MCOSTDY;
                    double MVKCOST = valuem + MCOSTPROD;
                    double cost = MVKCOST + costorder;
                    #region====QuantityRelated=============
                    int rown = 19;
                    exAgent.SetCellValue(rown, page, Math.Round(valuem, 2));
                    exAgent.SetCellValue(rown + 5, page, Math.Round(MVKCOST, 2));
                    double cprice = 0.001 * Convert.ToDouble(vkp["VKPPREIS"].ToString());
                    exAgent.SetCellValue(rown + 6, page, cprice);

                    string currency = dwVKP["CURRENCY"].ToString();
                    string PREH = dwVKP["PREH"].ToString();
                    if (currency.Contains("USD"))
                        cprice = cprice * exchange;
                    if (!PREH.Contains("M"))
                        cprice = cprice / 0.9144;

                    double MCOSTGROSS = Math.Round(cprice - cost, 2);
                    double MCOSTNET = Math.Round((cprice - cost) / 1.17, 2);
                    double MCOSTNETW = Math.Round(10000 * MCOSTNET / weftdensity);
                    exAgent.SetCellValue(rown + 12, page, MCOSTGROSS);
                    exAgent.SetCellValue(rown + 13, page, MCOSTNET);
                    exAgent.SetCellValue(rown + 14, page, MCOSTNETW);
                    if (qty == "")
                    {
                        qty += 0.01 * Convert.ToDouble(vkp["VKPMENGE1"].ToString()) + "(-" + Convert.ToInt16(vkp["VKPAUFPOS"].ToString()) + ")";
                    }
                    else
                    {
                        qty += Environment.NewLine + 0.01 * Convert.ToDouble(vkp["VKPMENGE1"].ToString()) + "(-" + Convert.ToInt16(vkp["VKPAUFPOS"].ToString()) + ")";
                    }
                    exAgent.SetCellValue(8, page, qty);
                    #endregion
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillInEXCEL()
        {
            try
            {
                string value = "";
                DataRow row = null;
                int x = 1, y = 1;
                int rown = 19;
                string processed = "";
                int posx = 18;
                int rpl = 9;
                DataRow dwVKP;
                dwVKP = dsSource.Tables["ICC_VKPRICE"].Rows[0];
                #region===Header============
                exAgent.SetCellValue(3, 1, GetGB2312Value(dwVKP["KDKUNDNA"].ToString()));
                exAgent.SetCellValue(3, 9, year + "年" + month + "月" + day + "日");
                exAgent.SetCellValue(35, 2, Helper.GetHRName(dsSource, dwVKP["NEWUSER"].ToString()));
                exAgent.SetCellValue(5, 3, dwVKP["VKPART"].ToString());
                exAgent.SetCellValue(6, 3, GetGB2312Value(dwVKP["VAABEZ"].ToString()));
                exAgent.SetCellValue(7, 3, GetGB2312Value(dwVKP["CONSTRUCTION"].ToString()));
                exAgent.SetCellValue(8, 1, "数量" + dwVKP["VKPUNIT"].ToString().Trim());
                #endregion

                rown = 19;

                #region=======后整理==========
                exAgent.SetCellValue(rown + 1, 3, dwVKP["MCOSTFIN"].ToString());
                exAgent.SetCellValue(rown + 2, 3, dwVKP["MCOSTPAK"].ToString());
                exAgent.SetCellValue(rown + 3, 3, dwVKP["MCOSTFVV"].ToString());
                exAgent.SetCellValue(rown + 4, 3, dwVKP["MCOSTFADD"].ToString());
                #endregion

                #region======Order=========
                //exAgent.SetCellValue(rown + 6, 3, dwVKP["MCPRICE"].ToString() + " " + dwVKP["CURRENCY"].ToString() + "/" + dwVKP["PREH"].ToString());
                exAgent.SetCellValue(rown + 6, 1, " 产品合同价(" + dwVKP["CURRENCY"].ToString() + "/" + dwVKP["PREH"].ToString() + ")" + dwVKP["VKKLIEFBED"].ToString());
                exAgent.SetCellValue(rown + 8, 3, dwVKP["MPRICEAGENT"].ToString());
                exAgent.SetCellValue(rown + 9, 3, dwVKP["MPRICESHIP"].ToString());
                exAgent.SetCellValue(rown + 10, 3, dwVKP["MPRICEFLIGHT"].ToString());
                exAgent.SetCellValue(rown + 11, 3, dwVKP["MCOSTADD"].ToString());
                exAgent.SetCellValue(rown + 15, 3, GetGB2312Value(dwVKP["REMARK"].ToString()));
                #endregion

                #region======Yarn============
                int count = 0;
                foreach (DataRow rx in dsSource.Tables["ICC_VKPWARP"].Rows)
                {
                    exAgent.SetCellValue(9, 3 + count * 2, GetGB2312Value(rx["MYARN"].ToString()));
                    exAgent.SetCellValue(10, 3 + count * 2, rx["MPRICEM"].ToString());
                    exAgent.SetCellValue(11, 3 + count * 2, rx["MUSAGE"].ToString());
                    exAgent.SetCellValue(12, 3 + count * 2, rx["MPRICED"].ToString());
                    exAgent.SetCellValue(13, 3 + count * 2, Math.Round(Convert.ToDouble(rx["MVALUEM"].ToString()) + Convert.ToDouble(rx["MVALUED"].ToString()), 2));
                    count++;
                }
                count = 0;
                foreach (DataRow rx in dsSource.Tables["ICC_VKPWEFT"].Rows)
                {
                    exAgent.SetCellValue(14, 3 + count * 2, GetGB2312Value(rx["MYARN"].ToString()));
                    exAgent.SetCellValue(15, 3 + count * 2, rx["MPRICEM"].ToString());
                    exAgent.SetCellValue(16, 3 + count * 2, rx["MUSAGE"].ToString());
                    exAgent.SetCellValue(17, 3 + count * 2, rx["MPRICED"].ToString());
                    exAgent.SetCellValue(18, 3 + count * 2, Math.Round(Convert.ToDouble(rx["MVALUEM"].ToString()) + Convert.ToDouble(rx["MVALUED"].ToString()), 2));
                    count++;
                }
                #endregion

            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }


        private string CreateFile(string orderno)
        {
            string filename = "";
            string sheetName = "";
            string Standard = "COWS";
            try
            {
                string pwd = "";
                sheetName = orderno;
                exAgent.Open(TemplatePath);
                exAgent.Company = strCompany;
                exAgent.strExcelFile = strExcelFile;
                exAgent.CreateNewSheet(orderno + ".xls", Standard, orderno.Replace(".xls", ""), pwd);
                filename = exAgent.FileName;
                FillInEXCEL();
                FillinRange();
                string localFile = exAgent.FileName;
                exAgent.Save();
                exAgent.Close();
                //System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return filename;
        }

        private void ServerShareFile(string ordernumer, string fileType, DataRow row = null)
        {
            string destfile, tempfile = "";
            string fileabsname = "销售预算-" + ordernumer;
            filename = destfolder + "\\" + fileabsname + ".xls"; ;
            destfile = filename;
            try
            {
                tempfile = CreateFile(ordernumer);

                if (File.Exists(destfile))
                {
                    File.Delete(destfile);
                }
                File.Move(tempfile, destfile);

                if (fileType == "OpenExcel")
                {
                    System.Diagnostics.Process.Start("EXCEL.EXE", filename);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                if (File.Exists(tempfile))
                {
                    File.Delete(tempfile);
                }
            }
        }
    }
}